﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using Sinacor.Infra.Service.Data;
using Sinacor.Infra.Service.Security.Authorization;
using Sinacor.Infra.Service.Common;

namespace Sinacor.Servico.Bovespa.Corretagem.ResourceAccess
{
    public class FormadorMercado : BaseResourceAccess
    {
        #region Construtor

        public FormadorMercado()
            : base(AuthorizationContext.Current.CompanyId)
        {
        }

        #endregion

        #region AlterarFormadorMercado
        /// <summary>
        /// Alteraro Formador Mercado
        /// </summary>
        /// <param name="request"></param>
        public void AlterarFormadorMercado(BusinessEntity.FormadorMercado request)
        {
            using (DbCommand command = this.DataBase.GetSqlStringCommand(" "))
            {
                #region SQL
                StringBuilder sql = new StringBuilder();

                sql.Append(@" UPDATE TORFORMADOR
                                SET VL_DESCONTO = :PercentualDesconto 
                                WHERE CD_CLIENTE = :CodigoCliente
                                AND CD_NEGOCIO = :Ativo
                                AND DT_INICIO = :DataInicial
                                AND DT_FIM = :DataFinal
                                AND COD_ISIN = :Isin
                                AND COD_TIPO_MERC = :TipoMercado");                                 
                
                #endregion

                #region Filtro
                this.DataBase.AddInParameter(command, "PercentualDesconto", DbType.Decimal, request.PercentualDesconto.Value);
                this.DataBase.AddInParameter(command, "CodigoCliente", DbType.Int32, request.CodigoCliente.Value);
                this.DataBase.AddInParameter(command, "Ativo", DbType.String, request.Ativo);
                this.DataBase.AddInParameter(command, "DataInicial", DbType.DateTime, request.DataInicial.Value);
                this.DataBase.AddInParameter(command, "DataFinal", DbType.DateTime, request.DataFinal.Value);
                this.DataBase.AddInParameter(command, "Isin", DbType.String, request.Isin);
                this.DataBase.AddInParameter(command, "TipoMercado", DbType.String, EnumConverter.ToString<BusinessEntity.Enum.TipoMercado>(request.TipoMercado));

                #endregion

               command.CommandText = sql.ToString();
               this.DataBase.ExecuteNonQuery(command);
            }
        }
        #endregion

        #region ConsultarConflitoPeriodo
        public List<BusinessEntity.FormadorMercado> ConsultarConflitoPeriodo(BusinessEntity.FormadorMercado request)
        {
            List<BusinessEntity.FormadorMercado> responses = null;
            StringBuilder sql = new StringBuilder();
            using (DbCommand command = this.DataBase.GetSqlStringCommand(" "))
            {
                #region SQL
                sql.Append(@"SELECT A.CD_CLIENTE CodigoCliente, 
                                B.NM_APELIDO ApelidoCliente, 
                                A.CD_NEGOCIO Ativo,
                                A.DT_INICIO DataInicial, 
                                A.DT_FIM DataFinal, 
                                A.VL_DESCONTO PercentualDesconto,
                                A.COD_TIPO_MERC TipoMercado
                            FROM TORFORMADOR A
                            INNER JOIN TSCCLIBOL B
                                    ON A.CD_CLIENTE = B.CD_CLIENTE
                            WHERE A.CD_CLIENTE = :CodigoCliente
                                  AND A.CD_NEGOCIO = :Ativo
                                  AND A.COD_ISIN = :Isin
                                  AND A.COD_TIPO_MERC = :TipoMercado
                                  AND ((:DataInicial BETWEEN A.DT_INICIO AND A.DT_FIM)
                                   OR (:DataFinal BETWEEN A.DT_INICIO AND A.DT_FIM)
                                   OR (A.DT_INICIO BETWEEN :DataInicial AND :DataFinal)
                                   OR (A.DT_FIM BETWEEN :DataInicial AND :DataFinal))");
                #endregion

                #region Filtro
                this.DataBase.AddInParameter(command, "CodigoCliente", DbType.Int32, request.CodigoCliente);
                this.DataBase.AddInParameter(command, "Ativo", DbType.String, request.Ativo);
                this.DataBase.AddInParameter(command, "DataInicial", DbType.DateTime, request.DataInicial);
                this.DataBase.AddInParameter(command, "DataFinal", DbType.DateTime, request.DataFinal);
                this.DataBase.AddInParameter(command, "Isin", DbType.String, request.Isin);
                this.DataBase.AddInParameter(command, "TipoMercado", DbType.String, EnumConverter.ToString<Corretagem.BusinessEntity.Enum.TipoMercado?>(request.TipoMercado));
                #endregion

                command.CommandText = sql.ToString();

                responses = this.FindAll<BusinessEntity.FormadorMercado>(command, new ConstructEntity<BusinessEntity.FormadorMercado>(ConstrutorConsultarConflitoPeriodo));

                return responses;

            }

        }
        #region ConstrutorConsultarConflitoPeriodo
        protected BusinessEntity.FormadorMercado ConstrutorConsultarConflitoPeriodo(IDataReader reader)
        {
            BusinessEntity.FormadorMercado entity = new BusinessEntity.FormadorMercado();

            entity.ApelidoCliente = reader.GetValue<System.String>("ApelidoCliente");
            entity.Ativo = reader.GetValue<System.String>("Ativo");
            entity.CodigoCliente = reader.GetValue<Int32?>("CodigoCliente");
            entity.DataInicial = reader.GetValue<System.DateTime?>("DataInicial");
            entity.DataFinal = reader.GetValue<System.DateTime?>("DataFinal");
            entity.PercentualDesconto = reader.GetValue<System.Decimal?>("PercentualDesconto");
            entity.Isin = reader.GetValue<System.String>("Isin");
            entity.TipoMercado = EnumConverter.Parse<BusinessEntity.Enum.TipoMercado>(reader.GetValue<System.String>("TipoMercado"));

            return entity;
        }
        #endregion

        #endregion

        #region ConsultarFormadoresMercado
        public List<BusinessEntity.FormadorMercado> ConsultarFormadoresMercado(BusinessEntity.Parameter.FiltroFormadorMercado request)
        {
            List<BusinessEntity.FormadorMercado> responses = null;
            StringBuilder sql = new StringBuilder();

            using (DbCommand command = this.DataBase.GetSqlStringCommand(" "))
            {
                sql.Append(@"SELECT A.CD_CLIENTE CodigoCliente, 
                                B.NM_APELIDO ApelidoCliente, 
                                A.CD_NEGOCIO Ativo,  
                                A.DT_INICIO DataInicial, 
                                A.DT_FIM DataFinal, 
                                A.COD_ISIN Isin,
                                A.VL_DESCONTO PercentualDesconto,
                                A.COD_TIPO_MERC TipoMercado
                            FROM 
                                TORFORMADOR A,
                                TSCCLIBOL B                                        
                            WHERE 
                                A.CD_CLIENTE = B.CD_CLIENTE");

                // Filtros Opcionais
                if (request.CodigoCliente.HasValue && request.CodigoCliente.Value != 0)
                {
                    sql.Append(" AND A.CD_CLIENTE = :CodigoCliente");
                    this.DataBase.AddInParameter(command, "CodigoCliente", DbType.Int32, request.CodigoCliente.Value);
                }
                
                if (!string.IsNullOrEmpty(request.ApelidoCliente))
                {
                    sql.Append(" AND B.NM_APELIDO = :ApelidoCliente");
                    this.DataBase.AddInParameter(command, "ApelidoCliente", DbType.String, request.ApelidoCliente);
                }

                if (!string.IsNullOrEmpty(request.Ativo))
                {
                    sql.Append(" AND A.CD_NEGOCIO = :Ativo");
                    this.DataBase.AddInParameter(command, "Ativo", DbType.String, request.Ativo);
                }

                if (request.DataInicial.HasValue)
                {
                    sql.Append(" AND A.DT_INICIO = :DataInicial");
                    this.DataBase.AddInParameter(command, "DataInicial", DbType.DateTime, request.DataInicial.Value);
                }

                if (request.DataFinal.HasValue)
                {
                    sql.Append(" AND A.DT_FIM = :DataFinal");
                    this.DataBase.AddInParameter(command, "DataFinal", DbType.DateTime, request.DataFinal.Value);
                }

                if (!string.IsNullOrEmpty(request.Isin))
                {
                    sql.Append(" AND A.COD_ISIN = :Isin");
                    this.DataBase.AddInParameter(command, "Isin", DbType.String, request.Isin);
                }

                if (request.PercentualDesconto.HasValue)
                {
                    sql.Append(" AND A.VL_DESCONTO = :PercentualDesconto");
                    this.DataBase.AddInParameter(command, "PercentualDesconto", DbType.Decimal, request.PercentualDesconto.Value);
                }

                if (request.TipoMercado.HasValue)
                {
                    sql.Append(" AND A.COD_TIPO_MERC = :TipoMercado");
                    this.DataBase.AddInParameter(command, "TipoMercado", DbType.String, EnumConverter.ToString<BusinessEntity.Enum.TipoMercado?>(request.TipoMercado));
                }

                sql.Append(" ORDER BY A.CD_CLIENTE");

                command.CommandText = sql.ToString();

                responses = this.FindAll<BusinessEntity.FormadorMercado>(command, new ConstructEntity<BusinessEntity.FormadorMercado>(ConstrutorConsultarFormadoresMercado));

                return responses;
            }
        }
        #region ConstrutorConsultarPoliticaCorretagem
        protected BusinessEntity.FormadorMercado ConstrutorConsultarFormadoresMercado(IDataReader reader)
        {
            BusinessEntity.FormadorMercado entity = new BusinessEntity.FormadorMercado();

            entity.ApelidoCliente = reader.GetValue<System.String>("ApelidoCliente");
            entity.Ativo = reader.GetValue<System.String>("Ativo");
            entity.CodigoCliente = reader.GetValue<Int32?>("CodigoCliente");
            entity.DataInicial = reader.GetValue<System.DateTime?>("DataInicial");
            entity.DataFinal = reader.GetValue<System.DateTime?>("DataFinal");
            entity.PercentualDesconto = reader.GetValue<System.Decimal?>("PercentualDesconto");
            entity.Isin = reader.GetValue<System.String>("Isin");
            entity.TipoMercado = EnumConverter.Parse<BusinessEntity.Enum.TipoMercado>(reader.GetValue<System.String>("TipoMercado"));

            return entity;
        }
        #endregion

        #endregion

        #region ExcluirFormadorMercado
        public void ExcluirFormadorMercado(BusinessEntity.FormadorMercado request)
        {
            using (DbCommand command = this.DataBase.GetSqlStringCommand(" "))
            {
                StringBuilder sql = new StringBuilder();

                sql.Append(@" DELETE 
                              FROM TORFORMADOR
                              WHERE CD_CLIENTE = :CodigoCliente
                                AND CD_NEGOCIO = :Ativo
                                AND COD_ISIN = :Isin
                                AND DT_INICIO = :DataInicial
                                AND DT_FIM = :DataFinal
                                AND COD_TIPO_MERC = :TipoMercado");

                this.DataBase.AddInParameter(command, "CodigoCliente", DbType.Int32, request.CodigoCliente);
                this.DataBase.AddInParameter(command, "Ativo", DbType.String, request.Ativo);
                this.DataBase.AddInParameter(command, "Isin", DbType.String, request.Isin);
                this.DataBase.AddInParameter(command, "DataInicial", DbType.DateTime, request.DataInicial.Value);
                this.DataBase.AddInParameter(command, "DataFinal", DbType.DateTime, request.DataFinal.Value);
                this.DataBase.AddInParameter(command, "TipoMercado", DbType.String, EnumConverter.ToString<Corretagem.BusinessEntity.Enum.TipoMercado?>(request.TipoMercado));

                command.CommandText = sql.ToString();
                this.DataBase.ExecuteNonQuery(command);
            }
        }

        #endregion

        #region IncluirFormadorMercado
        public void IncluirFormadorMercado(BusinessEntity.FormadorMercado request)
        {
            using (DbCommand command = this.DataBase.GetSqlStringCommand(" "))
            {
                StringBuilder sql = new StringBuilder();
                sql.Append(@" INSERT INTO TORFORMADOR 
                                (CD_CLIENTE, CD_NEGOCIO,COD_ISIN, DT_INICIO, DT_FIM, VL_DESCONTO,COD_TIPO_MERC) 
                              VALUES 
                                (:CodigoCliente, :Ativo,:Isin, :DataInicial, :DataFinal, :PercentualDesconto,:TipoMercado)");

                this.DataBase.AddInParameter(command, "CodigoCliente", DbType.Int32, request.CodigoCliente.Value);
                this.DataBase.AddInParameter(command, "Ativo", DbType.String, request.Ativo);
                this.DataBase.AddInParameter(command, "Isin", DbType.String, request.Isin);
                this.DataBase.AddInParameter(command, "DataInicial", DbType.DateTime, request.DataInicial.Value);
                this.DataBase.AddInParameter(command, "DataFinal", DbType.DateTime, request.DataFinal.Value);
                this.DataBase.AddInParameter(command, "PercentualDesconto", DbType.Decimal, request.PercentualDesconto.Value);
                this.DataBase.AddInParameter(command, "TipoMercado", DbType.String, EnumConverter.ToString<Corretagem.BusinessEntity.Enum.TipoMercado?>(request.TipoMercado));

                command.CommandText = sql.ToString();
                this.DataBase.ExecuteNonQuery(command);
            }
        }
        #endregion

    }
}
